package com.company.calendar.service;

import com.company.calendar.dto.HolidayDTO;
import com.company.calendar.entity.HolidayType;
import com.company.calendar.entity.ImportHistory;
import com.company.calendar.mapper.HolidayTypeMapper;
import com.company.calendar.mapper.ImportHistoryMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * Excel服务类
 *
 * @author System
 * @since 1.0.0
 */
@Service
@RequiredArgsConstructor
@Slf4j
@Transactional
public class ExcelService {

    private final HolidayService holidayService;
    private final HolidayTypeMapper holidayTypeMapper;
    private final ImportHistoryMapper importHistoryMapper;

    /**
     * 导入节假日Excel文件
     */
    public Map<String, Object> importHolidays(MultipartFile file, Long companyId) {
        log.info("导入节假日Excel文件: 公司ID={}, 文件名={}", companyId, file.getOriginalFilename());

        Map<String, Object> result = new HashMap<>();
        int successCount = 0;
        int failCount = 0;
        List<String> errorMessages = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);

            // 获取节假日类型映射
            Map<String, Long> typeCodeMap = holidayTypeMapper.findByIsDeletedFalse()
                    .stream()
                    .collect(HashMap::new, (map, type) -> map.put(type.getTypeCode(), type.getId()), HashMap::putAll);

            // 从第二行开始读取数据（第一行是标题）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                try {
                    HolidayDTO holidayDTO = parseHolidayRow(row, companyId, typeCodeMap);
                    holidayService.createHoliday(holidayDTO);
                    successCount++;
                } catch (Exception e) {
                    failCount++;
                    errorMessages.add("第" + (i + 1) + "行: " + e.getMessage());
                    log.error("导入第{}行失败: {}", i + 1, e.getMessage());
                }
            }

        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            throw new RuntimeException("读取Excel文件失败: " + e.getMessage());
        }

        // 保存导入历史
        ImportHistory importHistory = new ImportHistory();
        importHistory.setCompanyId(companyId);
        importHistory.setImportType("HOLIDAY");
        importHistory.setFileName(file.getOriginalFilename());
        importHistory.setImportCount(successCount + failCount);
        importHistory.setSuccessCount(successCount);
        importHistory.setFailCount(failCount);
        importHistory.setStatus(failCount == 0 ? "SUCCESS" : (successCount == 0 ? "FAIL" : "PARTIAL"));
        importHistory.setErrorMessage(String.join("; ", errorMessages));
        importHistoryMapper.insert(importHistory);

        result.put("successCount", successCount);
        result.put("failCount", failCount);
        result.put("errorMessages", errorMessages);
        result.put("status", importHistory.getStatus());

        return result;
    }

    /**
     * 导出节假日Excel文件
     */
    public byte[] exportHolidays(Long companyId, Integer year) {
        log.info("导出节假日Excel文件: 公司ID={}, 年份={}", companyId, year);

        List<HolidayDTO> holidays = holidayService.getHolidaysByCompanyIdAndYear(companyId, year);

        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("节假日");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            String[] headers = {"节假日名称", "日期", "类型", "是否工作日", "描述"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);

                // 设置标题样式
                CellStyle headerStyle = workbook.createCellStyle();
                Font headerFont = workbook.createFont();
                headerFont.setBold(true);
                headerStyle.setFont(headerFont);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            for (int i = 0; i < holidays.size(); i++) {
                HolidayDTO holiday = holidays.get(i);
                Row row = sheet.createRow(i + 1);

                row.createCell(0).setCellValue(holiday.getHolidayName());
                row.createCell(1).setCellValue(holiday.getHolidayDate().format(formatter));
                row.createCell(2).setCellValue(holiday.getTypeName());
                row.createCell(3).setCellValue(holiday.getIsWorkday() ? "是" : "否");
                row.createCell(4).setCellValue(holiday.getDescription() != null ? holiday.getDescription() : "");
            }

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // 转换为字节数组
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            return outputStream.toByteArray();

        } catch (IOException e) {
            log.error("导出Excel文件失败", e);
            throw new RuntimeException("导出Excel文件失败: " + e.getMessage());
        }
    }

    /**
     * 解析节假日行数据
     */
    private HolidayDTO parseHolidayRow(Row row, Long companyId, Map<String, Long> typeCodeMap) {
        HolidayDTO holidayDTO = new HolidayDTO();
        holidayDTO.setCompanyId(companyId);

        // 节假日名称
        Cell nameCell = row.getCell(0);
        if (nameCell == null || nameCell.getCellType() == CellType.BLANK) {
            throw new RuntimeException("节假日名称不能为空");
        }
        holidayDTO.setHolidayName(getCellStringValue(nameCell));

        // 日期
        Cell dateCell = row.getCell(1);
        if (dateCell == null || dateCell.getCellType() == CellType.BLANK) {
            throw new RuntimeException("日期不能为空");
        }
        LocalDate holidayDate = parseDate(getCellStringValue(dateCell));
        holidayDTO.setHolidayDate(holidayDate);

        // 类型编码
        Cell typeCell = row.getCell(2);
        if (typeCell == null || typeCell.getCellType() == CellType.BLANK) {
            throw new RuntimeException("类型编码不能为空");
        }
        String typeCode = getCellStringValue(typeCell);
        Long typeId = typeCodeMap.get(typeCode);
        if (typeId == null) {
            throw new RuntimeException("类型编码不存在: " + typeCode);
        }
        holidayDTO.setTypeId(typeId);

        // 是否工作日
        Cell workdayCell = row.getCell(3);
        if (workdayCell != null && workdayCell.getCellType() != CellType.BLANK) {
            String workdayStr = getCellStringValue(workdayCell);
            holidayDTO.setIsWorkday("是".equals(workdayStr) || "true".equalsIgnoreCase(workdayStr) || "1".equals(workdayStr));
        } else {
            holidayDTO.setIsWorkday(false);
        }

        // 描述
        Cell descCell = row.getCell(4);
        if (descCell != null && descCell.getCellType() != CellType.BLANK) {
            holidayDTO.setDescription(getCellStringValue(descCell));
        }

        return holidayDTO;
    }

    /**
     * 获取单元格字符串值
     */
    private String getCellStringValue(Cell cell) {
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue().trim();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue().toString();
            } else {
                return String.valueOf((long) cell.getNumericCellValue());
            }
        } else {
            return cell.toString().trim();
        }
    }

    /**
     * 解析日期字符串
     */
    private LocalDate parseDate(String dateStr) {
        try {
            // 尝试多种日期格式
            String[] patterns = {"yyyy-MM-dd", "yyyy/MM/dd", "MM/dd/yyyy", "dd/MM/yyyy"};
            for (String pattern : patterns) {
                try {
                    return LocalDate.parse(dateStr, DateTimeFormatter.ofPattern(pattern));
                } catch (Exception ignored) {
                }
            }
            throw new RuntimeException("无法解析日期格式: " + dateStr);
        } catch (Exception e) {
            throw new RuntimeException("日期格式错误: " + dateStr);
        }
    }
}


